﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class RC_MoveOfficialPlusBLL


    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);


        //ent_pos_name = txtEntPosition.Text;
        //ent_pos_type_seq = int.Parse(entPostTypeCombo.SelectedValue);
        //ent_class_seq = int.Parse(entClassCombo.SelectedValue);
        //ent_no = txtEntNo.Text;
        //ent_date = txtEntDate.GetStringValue();
        //ent_job_site_name = txtEntJobSiteName.Text;  


        public void insert(int cmd_seq,int emp_seq,int move_seq,int edu_his_seq,string old_pos_name, 
    int old_pos_type_seq ,int old_class_seq ,string old_job_site_name, double old_salary ,double salary,
    string new_pos_name ,string new_pos_ref,int new_class_seq,int new_job_site_seq ,string new_job_site_name
, string start_date, int new_pos_type_seq,string remarks, int created_by,string ent_pos_name,int ent_pos_type_seq,
            int ent_class_seq,int ent_no,string ent_date,string ent_job_site_name,string new_class_name)
        {
            try
                          
            {
                DateTime startDate,entDate;

                startDate  = Convert.ToDateTime(start_date);
                entDate = Convert.ToDateTime(ent_date); 
  
           //     startDate = Convert.ToDateTime(Utils.Date.ToEngFormatDate(start_date));     
                
                dbManager.Open();
                dbManager.CreateParameters(28);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@move_seq", move_seq);
                dbManager.AddParameters(3, "@edu_his_seq", edu_his_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);
                dbManager.AddParameters(9, "@salary",salary);
                dbManager.AddParameters(10, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(11, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(12, "@new_class_seq", new_class_seq);
                dbManager.AddParameters(13, "@new_job_site_seq", new_job_site_seq);
                dbManager.AddParameters(14, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(15, "@start_date", startDate);
                dbManager.AddParameters(16, "@remarks", remarks);
                dbManager.AddParameters(17, "@record_status", SystemInfo._ActiveRecordStatus  );
                dbManager.AddParameters(18, "@created_by", created_by);
                dbManager.AddParameters(19, "@created_when", DateTime.Today);
                dbManager.AddParameters(20, "@new_pos_type_seq", new_pos_type_seq);

                dbManager.AddParameters(21, "@ent_pos_name", ent_pos_name);
                dbManager.AddParameters(22, "@ent_pos_type_seq", ent_pos_type_seq);
                dbManager.AddParameters(23, "@ent_class_seq", ent_class_seq);
                dbManager.AddParameters(24, "@ent_no", ent_no);
                dbManager.AddParameters(25, "@ent_date", entDate);
                dbManager.AddParameters(26, "@ent_job_site_name", ent_job_site_name);
                dbManager.AddParameters(27, "@new_class_name", new_class_name );



           
                  string sql;





                  sql = " insert into hrm_ps_command_dtls(cmd_seq,emp_seq,move_seq,edu_his_seq,old_pos_name,"
      + " old_pos_type_seq ,old_class_seq ,old_job_site_name,old_salary ,new_salary,"
      + " new_pos_name ,new_pos_ref,new_class_seq,new_job_site_seq ,new_job_site_name,"
      + " start_date,remarks,record_status,new_pos_type_seq,created_by,created_when,ent_pos_name,ent_pos_type_seq,"
      + "ent_class_seq,ent_no,ent_date,ent_job_site_name,new_class_name)"


      + " values(@cmd_seq,@emp_seq,@move_seq,@edu_his_seq,@old_pos_name,"
      + " @old_pos_type_seq ,@old_class_seq ,@old_job_site_name,@old_salary ,@salary,"
      + " @new_pos_name ,@new_pos_ref,@new_class_seq,@new_job_site_seq ,@new_job_site_name,"
      + " @start_date,@remarks,@record_status,@new_pos_type_seq,@created_by,@created_when,"
      + " @ent_pos_name,@ent_pos_type_seq,"
      + " @ent_class_seq,@ent_no,@ent_date,@ent_job_site_name,@new_class_name)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }




        public void Update(int cmd_dtls_seq, int move_seq, int edu_his_seq, string old_pos_name,
    int old_pos_type_seq, int old_class_seq, string old_job_site_name, double old_salary, double salary,
    string new_pos_name, string new_pos_ref, int new_class_seq, int new_job_site_seq, string new_job_site_name
, string start_date, int new_pos_type_seq, string remarks, int updated_by, string ent_pos_name, int ent_pos_type_seq,
            int ent_class_seq, int ent_no, string ent_date, string ent_job_site_name, string new_class_name)
        {
            try
            {
                DateTime startDate, entDate;

                startDate = Convert.ToDateTime(start_date);
                entDate = Convert.ToDateTime(ent_date);

                //     startDate = Convert.ToDateTime(Utils.Date.ToEngFormatDate(start_date));     

                dbManager.Open();
                dbManager.CreateParameters(26);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@move_seq", move_seq);
                dbManager.AddParameters(2, "@new_class_name", new_class_name);
                dbManager.AddParameters(3, "@edu_his_seq", edu_his_seq);
                dbManager.AddParameters(4, "@old_pos_name", old_pos_name);
                dbManager.AddParameters(5, "@old_pos_type_seq", old_pos_type_seq);
                dbManager.AddParameters(6, "@old_class_seq", old_class_seq);
                dbManager.AddParameters(7, "@old_job_site_name", old_job_site_name);
                dbManager.AddParameters(8, "@old_salary", old_salary);
                dbManager.AddParameters(9, "@new_salary", salary);
                dbManager.AddParameters(10, "@new_pos_name", new_pos_name);
                dbManager.AddParameters(11, "@new_pos_ref", new_pos_ref);
                dbManager.AddParameters(12, "@new_class_seq", new_class_seq);
                dbManager.AddParameters(13, "@new_job_site_seq", new_job_site_seq);
                dbManager.AddParameters(14, "@new_job_site_name", new_job_site_name);
                dbManager.AddParameters(15, "@start_date", startDate);
                dbManager.AddParameters(16, "@remarks", remarks);
                dbManager.AddParameters(17, "@updated_by", updated_by);
                dbManager.AddParameters(18, "@updated_when", DateTime.Today);
                dbManager.AddParameters(19, "@new_pos_type_seq", new_pos_type_seq);
                dbManager.AddParameters(20, "@ent_pos_name", ent_pos_name);
                dbManager.AddParameters(21, "@ent_pos_type_seq", ent_pos_type_seq);
                dbManager.AddParameters(22, "@ent_class_seq", ent_class_seq);
                dbManager.AddParameters(23, "@ent_no", ent_no);
                dbManager.AddParameters(24, "@ent_date", entDate);
                dbManager.AddParameters(25, "@ent_job_site_name", ent_job_site_name);


                string sql;

                sql = " update hrm_ps_command_dtls set"
                + " move_seq=@move_seq,"
                + " edu_his_seq=@edu_his_seq,"
                + " old_pos_name=@old_pos_name,"
                + " old_pos_type_seq=@old_pos_type_seq,"
                + " old_class_seq=@old_class_seq,"
                + " old_job_site_name=@old_job_site_name,"
                + " old_salary=@old_salary,"
                + " new_salary=@new_salary,"
                + " new_pos_name=@new_pos_name,"
                + " new_pos_ref=@new_pos_ref,"
                + " new_class_seq=@new_class_seq,"
                + " new_job_site_seq=@new_job_site_seq,"
                + " new_job_site_name=@new_job_site_name,"
                + " start_date=@start_date,"
                + " remarks=@remarks,"
                + " new_pos_type_seq=@new_pos_type_seq,"
                + " updated_by=@updated_by,"
                + " updated_when=@updated_when,"
                + " ent_pos_name=@ent_pos_name,"
                + " ent_pos_type_seq=@ent_pos_type_seq,"
                + " ent_class_seq=@ent_class_seq,"
                + " ent_no=@ent_no,"
                + " ent_date=@ent_date,"
                + " ent_job_site_name=@ent_job_site_name,"
                + " new_class_name=@new_class_name"
                + " where cmd_dtls_seq=" + cmd_dtls_seq;


                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            finally
            {
                dbManager.Dispose();

            }

        }





        public DataTable Select(int cmd_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = "SELECT"
                   + " concat(prefix_name,'  ',first_name,'    ',last_name) AS full_name,"
                   + " cmd_dtls.cmd_dtls_seq,"
                   + " cmd_dtls.*,"
                   + " cmd_dtls.emp_seq,"
                   + " cmd_dtls.remarks,"
                   + " cmd_dtls.start_date,"
                   + " cmd_dtls.old_salary,"
                   + " cmd_dtls.old_pos_ref,"
                   + " cmd_dtls.old_pos_name,"
                   + " cmd_dtls.old_class_seq,"
                   + " cmd_dtls.old_job_site_seq,"
                   + " cmd_dtls.old_job_site_name,"
                   + " cmd_dtls.new_pos_seq,"
                   + " cmd_dtls.new_pos_ref,"
                   + " cmd_dtls.new_pos_name,"
                   + " cmd_dtls.new_class_seq,"
                   + " cmd_dtls.new_job_site_seq,"
                   + " cmd_dtls.new_job_site_name,"
                   + " cmd_dtls.move_seq,"
                   + " hrm_ps_employees.citizen,"
                   + " hrm_ps_employees.birth_date,"
                   + " cmd_dtls.new_salary,"
                  + " hrm_ps_employees.date_quit,"
                   + " mas_name_prefixes.prefix_name,"
                   + " hrm_mp_classes.class_name"
                   + " FROM"
                   + " hrm_ps_command_dtls AS cmd_dtls"
                   + " Inner Join hrm_ps_employees ON hrm_ps_employees.emp_seq = cmd_dtls.emp_seq"
                   + " Inner Join mas_name_prefixes ON mas_name_prefixes.name_prefix_seq = hrm_ps_employees.name_prefix_seq"
                   + " Inner Join hrm_mp_classes ON hrm_mp_classes.class_seq = cmd_dtls.new_class_seq"
                   + " where cmd_dtls.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
                   + " and cmd_dtls.cmd_seq=" + cmd_seq;


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }



        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from mas_record_statuses  "
                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }

        
 
        public void Delete(int cmd_dtls_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
               

                sql = " update hrm_ps_command_dtls set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where cmd_dtls_seq = @cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable SelectDialogOldPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogEntPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select field_pos_seq,field_pos_line"
                    + " from hrm_mp_field_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }

        public DataTable SelectDialogPos()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select *"
                    + " from hrm_mp_positions"
+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";






                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }


        public DataTable SelectComboTmpEdu(int tmp_emp_seq)
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select "
+ " qua.edu_qual_name,"
+ " qua.edu_qual_seq,"
+ " emp_edu.tmp_emp_seq"
+ " FROM"
+ " mas_edu_qualifications AS qua"
+ " Inner Join hrm_tmp_employee_educations AS emp_edu ON qua.edu_qual_seq = emp_edu.edu_qual_seq"
+ " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_edu.tmp_emp_seq=" + tmp_emp_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }



        }



        public DataTable BindTxtMajor(int edu_qual_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " emp_edu.edu_qual_seq,"
+ " major.edu_major_name,"
+ " ins.edu_ins_name"
+ " FROM"
+ " mas_edu_majors AS major"
+ " Inner Join hrm_tmp_employee_educations AS emp_edu ON major.edu_major_seq = emp_edu.edu_major_seq"
+ " Inner Join mas_edu_institutes AS ins ON emp_edu.edu_ins_seq = ins.edu_ins_seq"

+ " where emp_edu.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and emp_edu.edu_qual_seq = " + edu_qual_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }




        public DataTable BindPosition(int pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " pos.pos_seq,"
+ " pos.pos_ref,"
+ " pos.pos_name,"
+ " pos.job_site_seq,"
+ " field_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_positions AS pos"
+ " Inner Join hrm_mp_field_positions AS field_pos ON pos.field_pos_seq = field_pos.field_pos_seq"

+ " where pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and pos.field_pos_seq = " + pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }


        public DataTable BindPositionType(int field_pos_seq)
        {

            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT"
+ " field_pos.field_pos_seq,"
+ " field_pos.field_pos_line,"
+ " type_pos.pos_type_name,"
+ " type_pos.pos_type_seq"
+ " FROM"
+ " hrm_mp_field_positions AS field_pos"
+ " Inner Join hrm_mp_position_types AS type_pos ON field_pos.pos_type_seq = type_pos.pos_type_seq"

+ " where field_pos.record_status = '" + SystemInfo._ActiveRecordStatus + "'"
+ " and field_pos.field_pos_seq = " + field_pos_seq;

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }


            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }


        }





    







     

   
    }
}
